﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Models;
using System.Data;
using System.Data.SqlClient;

namespace PBOffice.DAL
{
    public class AccFileService
    {
        public List<AccFile> GetAllAccFileByFileId(int id)
        {
            List<AccFile> list = new List<AccFile>();
            string sql = string.Format("select a.AccessoryId,a.AccessoryName,a.AccessorySize,a.FileId,a.CreateDate,a.AccessoryPath,f.FileTypeId,f.FileTypeName"
            + " from AccessoryFile a, FileTypeInfo f where a.AccessoryType=f.FileTypeId and a.FileId={0}", id);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    AccFile acc = new AccFile();
                    FileTypeInfo type = new FileTypeInfo();
                    type.FileTypeId = (int)dr["FileTypeId"];
                    type.FileTypeName = dr["FileTypeName"].ToString();
                    acc.AccId = (int)dr["AccessoryId"];
                    acc.AccName = dr["AccessoryName"].ToString();
                    acc.AccSize = (int)dr["AccessorySize"];
                    acc.FileId = (int)dr["FileId"];
                    acc.CreateDate = Convert.ToDateTime(dr["CreateDate"]);
                    acc.AccPath = dr["AccessoryPath"].ToString();
                    acc.AccType = type;
                    list.Add(acc);
                }
            }
            return list;
        }
        public void DeleteAccFile(int accId, int FileId)
        {
            string sql = string.Format("delete [AccessortFile] where AccessoryId={0} and FileId={1}", accId, FileId);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public void DeleteAccFile(int FileId)
        {
            string sql = string.Format("delete [AccessortFile] where  FileId={0}", FileId);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public void AddAccFile(AccFile acc)
        {
            string sql = "insert [AccessoryFile] (FileId,AccessoryName,AccessorySize,AccessoryType,CreateDate,AccessoryPath) values"
            + "(@FileId,@AccessoryName,@AccessorySize,@AccessoryType,@CreateDate,@AccessoryPath)";
            SqlParameter[] spa = {
                                     new SqlParameter("@FileId",acc.FileId),
                                     new SqlParameter("@AccessoryName",acc.AccName),
                                     new SqlParameter("@AccessorySize",acc.AccSize),
                                     new SqlParameter("@AccessoryType",acc.AccType.FileTypeId),
                                     new SqlParameter("@CreateDate",acc.CreateDate),
                                     new SqlParameter("@AccessoryPath",acc.AccPath)
                                     
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
    }
}
